package cn.edu.swu.book;

import cn.edu.swu.core.AbstractRepo;
import cn.edu.swu.core.ResultSetVisitor;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

//功能：把逻辑访问的结果放到数据库中，对数据库进行相关的访问（增删改查）
public class BookRepo extends AbstractRepo {
    /*
    //获取数据库链接
    private Connection getConnection () throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver"); //让tomcat加载mysql的驱动程序
        String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/bookstore";
        String dbUser = "root";
        String dbPass = "zmj410310";

        Connection connection = DriverManager.getConnection(jdbcUrl, dbUser, dbPass);
        return connection;
    }
    */

    private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");

    public List<Book> getAllBooks() throws SQLException, ClassNotFoundException {
        //System.out.println("do mysql database select!");
        List<Book> books = new ArrayList<>(); //容器
        String sql = "SELECT * FROM book";
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });

        /*
        //1.连接数据库
        //放到try里面会自动关闭数据库连接
        //凡是继承了AutoCloseable接口的就可以放到try里面自动关闭（Connection继承了AutoCloseable接口）
        try (Connection connection = getConnection()) {
            //2.创建一个statement
            try (Statement statement = connection.createStatement()) {
                //3.查询
                String sql = "SELECT * FROM book";
                ResultSet resultSet = statement.executeQuery(sql); //返回数据集
                //逐条取出每条数据
                while (resultSet.next()) {
                    Book book = new Book();
                    book.setId(resultSet.getInt("id"));
                    book.setName(resultSet.getString("name"));
                    book.setAuthor(resultSet.getString("author"));
                    book.setPrice(resultSet.getDouble("price"));
                    book.setDescription(resultSet.getString("description"));
                    book.setPublish(resultSet.getDate("publish"));

                    //从数据库中每取出一条数据构造好一个book后，放到books这个List里
                    books.add(book);
                }
            }
        }
         */

        //books.forEach(e -> System.out.println(e.toString())); //打印出来
        return books;
    }

    public void save(Book book) throws SQLException {
        if (book.getId() < 0) {
            this.add(book);
        } else {
            this.update(book);
        }
    }

    public void add(Book book) throws SQLException {
        String template = "INSERT INTO book (name, author, price, description, publish) " +
                "values ('%s', '%s', %s, '%s', '%s')";
        this.execute(String.format(template, book.getName(), book.getAuthor(), book.getPrice(),
                book.getDescription(), dateFormat.format(book.getPublish()), book.getImage()));
    }

    public void update(Book book) throws SQLException {
        String template = "UPDATE book " +
                "SET name='%s', author='%s', price=%s, description='%s', publish='%s', image='%s' " +
                "WHERE id=%s";
        this.execute(String.format(template, book.getName(), book.getAuthor(), book.getPrice(),
                book.getDescription(), dateFormat.format(book.getPublish()), book.getImage(), book.getId()));
    }

    public void deleteById(int id) throws SQLException {
        String template = "DELETE FROM book WHERE id = %s";
        this.execute(String.format(template, id));
    }

    public Book getById(int id) throws SQLException {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE id=" + id;
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });
        return books.size() > 0 ? books.get(0) : null;
    }

    public List<Book> search(String key) throws SQLException {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE name LIKE '%" + key + "%' " +
                "OR author LIKE '%" + key + "%'";
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });
        return books;
    }

    private Book getOneRecord(ResultSet rs) throws SQLException {
        Book book = new Book();
        book.setId(rs.getInt("id"));
        book.setName(rs.getString("name"));
        book.setAuthor(rs.getString("author"));
        book.setPrice(rs.getDouble("price"));
        book.setDescription(rs.getString("description"));
        book.setPublish(rs.getDate("publish"));
        book.setImage(rs.getString("image"));
        return book;
    }
}